import os
import time
import random
import sqlite3
import numpy as np
import matplotlib
from .._config import UI_RES_PATH
from common.settings import Settings

matplotlib.rcParams['backend'] = 'Qt5Agg'
import matplotlib.pyplot as plt
import datetime, time
from PyQt5 import QtWidgets
from PyQt5.QtCore import Qt, pyqtSlot, QUrl, QDate
from PyQt5.QtWidgets import QMainWindow, QAction, QToolBar, QAbstractItemView, QHeaderView
import pandas as pd
from .BaseWindow import BaseWindow
from ..widgets.DataCanvas import DataCanvas
from PyQt5.QtGui import QDesktopServices, QFont
import xlwt


class UiBaseDataBoard(QMainWindow, BaseWindow):
    def __init__(self, db: str = ''):
        QMainWindow.__init__(self)
        BaseWindow.__init__(self)
        self.page_cnt = 1  # 总页数
        self.db_path = db  # 数据库路径
        self.table_name = 'RESULT_DATA'  # 表名
        self.table_header = []  # 表头
        self.table_limit = 100  # 每页数据条目总数
        self.result = None  # 数据库查询结果
        self.sql = f'select * from {self.table_name}'

        super(UiBaseDataBoard, self).__init__()

        # 显示中文
        # plt.rcParams['font.family'] = 'Microsoft YaHei'
        # plt.rcParams['font.sans-serif'] = ['Microsoft YaHei']
        # 显示字符
        plt.rcParams['axes.unicode_minus'] = False
        self.resize(1700, 900)
        self.setWindowTitle('数据展示')
        self.data = ''
        self.defect_list = list(Settings.DEFECT_MAP.values())

    def showEvent(self, event):
        self.SetWindowCentered()
        if os.path.exists(self.db_path):
            self.initData()

    def closeEvent(self, event):
        if os.path.exists(self.db_path):
            self.conn.close()

    def _connect(self):
        self.action_compute.triggered.connect(lambda: self.dock_compute.setVisible(not (self.dock_compute.isVisible())))
        self.action_chart.triggered.connect(lambda: self.dock_chart.setVisible(not (self.dock_chart.isVisible())))

        self.P_CURRENT.valueChanged.connect(self.page_content)
        self.BTN_QUERY.clicked.connect(self.db_query)
        self.BTN_EXPORT.clicked.connect(self.export_xls)

    def _initUI(self):
        self.central = QtWidgets.QWidget(self)
        self.grid_main = QtWidgets.QGridLayout(self.central)

        self.toolbar = QToolBar(self)
        self.toolbar.setMovable(False)
        self.toolbar.setToolButtonStyle(Qt.ToolButtonTextBesideIcon)
        self.toolbar.setStyleSheet("background-color: rgb(0,175,219);color: black;")
        self.toolbar.setFixedHeight(45)
        self.addToolBar(Qt.TopToolBarArea, self.toolbar)

        self.action_compute: QAction
        self.action_chart: QAction
        for (var, txt) in [
            ('compute', '  数据统计  '),
            ('chart', '   数据图表  ')
        ]:
            self.QActionPack(var, txt, self.toolbar, str(UI_RES_PATH / ('%s.png' % var)))

        self.TABLE = QtWidgets.QTableWidget(self)
        self.TABLE.setEditTriggers(QAbstractItemView.NoEditTriggers)
        self.TABLE.horizontalHeader().setStretchLastSection(True)

        self.splitter_page = QtWidgets.QSplitter(self)
        self.splitter_page.setHandleWidth(0)
        self.splitter_page.setChildrenCollapsible(False)

        self.label_0 = QtWidgets.QLabel('第', self)
        self.P_CURRENT = QtWidgets.QSpinBox(self)
        self.P_CURRENT.setMinimum(1)
        self.label_1 = QtWidgets.QLabel('页', self)
        self.label_2 = QtWidgets.QLabel('/共', self)
        self.P_TOTAL = QtWidgets.QLabel('1', self)
        self.label_3 = QtWidgets.QLabel('页', self)
        self.splitter_page.addWidget(self.label_0)
        self.splitter_page.addWidget(self.P_CURRENT)
        self.splitter_page.addWidget(self.label_1)
        self.splitter_page.addWidget(self.label_2)
        self.splitter_page.addWidget(self.P_TOTAL)
        self.splitter_page.addWidget(self.label_3)

        self.grid_main.addWidget(self.TABLE, 0, 0, 1, 2)
        self.grid_main.addWidget(self.splitter_page, 1, 0)

        self.grid_main.setColumnStretch(1, 1)

        self.QDockBand('compute', '数据统计', 400, None, Qt.LeftDockWidgetArea)
        self.QDockBand('chart', '数据图表', 600, None, Qt.RightDockWidgetArea)

        self.setCentralWidget(self.central)

    def initData(self):
        self.conn = sqlite3.connect(str(self.db_path))
        self.cursor = self.conn.cursor()
        self.P_CURRENT.setValue(1)
        self.page_content()
        # self.calculate()

    def ui_table_h_header(self, v: list):
        self.TABLE.setColumnCount(len(v))
        self.TABLE.setHorizontalHeaderLabels(v)

    def _dockContent(self, var, parent, grid):
        if var == 'chart':
            chart_name = var.upper()
            setattr(self, chart_name, DataCanvas(parent))
            chart_obj: DataCanvas = getattr(self, chart_name)
            grid.addWidget(chart_obj, 0, 0)
        elif var == 'compute':
            s_label = QtWidgets.QLabel('开始时间')
            t_label = self.QDateTimePack('QUERY_TIME_BEGIN', parent)
            t_label.setFixedSize(180, 40)
            s_label2 = QtWidgets.QLabel('结束时间')
            t_label2 = self.QDateTimePack('QUERY_TIME_END', parent)
            t_label2.setFixedSize(180, 40)
            # time_grid = QtWidgets.QGridLayout(parent)
            grid.addWidget(s_label, 0, 0)
            grid.addWidget(t_label, 0, 1)
            grid.addWidget(s_label2, 1, 0)
            grid.addWidget(t_label2, 1, 1)
            # grid.addLayout(time_grid)
            # grid.addWidget(QtWidgets.QLabel('开始时间', parent))
            # grid.addWidget(self.QDateTimePack('QUERY_TIME_BEGIN', parent))
            # grid.addWidget(QtWidgets.QLabel('结束时间', parent))
            # grid.addWidget(self.QDateTimePack('QUERY_TIME_END', parent))

            # splitter_btn = self.QSplitterPack('btn', parent)
            # splitter_btn.setHandleWidth(3)
            self.BTN_QUERY = QtWidgets.QPushButton('查询', parent)
            self.BTN_QUERY.setFixedHeight(35)
            self.BTN_QUERY.setStyleSheet('color: rgb(32, 74, 135)')
            self.BTN_QUERY.setStyleSheet("QPushButton {"
                                         "background-color: rgb(0,175,219);"  # 设置背景颜色
                                         "color: white;"  # 设置文本颜色
                                         "border-radius: 5px;"  # 设置边框圆角
                                         "border-width: 2px;"
                                         "border-color: rgb(108, 166, 205);"
                                         "border-style: outset;"
                                         "}"
                                         "QPushButton:hover {background-color: rgb(108, 166, 205);}"
                                         "QPushButton:pressed {background-color: rgb(74, 112, 139);}")

            # splitter_btn = self.QSplitterPack('btn', parent)
            # splitter_btn.setHandleWidth(3)
            self.BTN_EXPORT = QtWidgets.QPushButton('导出', parent)
            self.BTN_EXPORT.setFixedHeight(35)
            self.BTN_EXPORT.setStyleSheet('color: rgb(32, 74, 135)')
            self.BTN_EXPORT.setStyleSheet("QPushButton {"
                                          "background-color: rgb(0,175,219);"  # 设置背景颜色
                                          "color: white;"  # 设置文本颜色
                                          "border-radius: 5px;"  # 设置边框圆角
                                          "border-width: 2px;"
                                          "border-color: rgb(108, 166, 205);"
                                          "border-style: outset;"
                                          "}"
                                          "QPushButton:hover {background-color: rgb(108, 166, 205);}"
                                          "QPushButton:pressed {background-color: rgb(74, 112, 139);}")

            grid.addWidget(self.BTN_QUERY, 2, 0)
            grid.addWidget(self.BTN_EXPORT, 2, 1)

            grid.addWidget(self.QDivider(parent))

            for var, text, items in [
                ('DATA_KIND', '类别', list(Settings.DEFECT_MAP.values())
                 ),
            ]:
                self.QDuet(parent, grid, (QtWidgets.QLabel, QtWidgets.QComboBox), var, text=text, items=items)
            # self.DATA_KIND.setCurrentIndex(-1)
            self.DATA_KIND.currentTextChanged.connect(self.calculate)

            for var, text in [
                ('VAR', '方差'),
                ('MEAN', '平均差'),
                ('STD', '标准差σ'),
                ('MEAN_3STD0', '平均差\n-3σ'),
                ('MEAN_3STD1', '平均差\n+3σ'),
                ('STD3_AREA', '3σ\n水平'),
                ('STD6_AREA', '6σ\n水平'),
                ('CMK', 'CMK\n水平'),
            ]:
                self.QDuet(parent, grid, (QtWidgets.QLabel, QtWidgets.QLineEdit), var, text=text)
                getattr(self, var).setEnabled(False)

    @pyqtSlot()
    def db_query(self):
        """ 根据筛选条件查询数据库 """
        self.sql = f'select * from {self.table_name}'

        time_begin = self.QUERY_TIME_BEGIN.dateTime().toTime_t()
        time_end = self.QUERY_TIME_END.dateTime().toTime_t()
        self.sql = ' '.join([self.sql, 'where', f'时间 between {time_begin} and {time_end}'])
        self.page_content()

    def calculate(self):
        """ 统计查询结果 """
        kind_txt = self.DATA_KIND.currentText()
        data_list = [0] * len(self.defect_list)
        data_size_list = [[] for i in range(len(self.defect_list))]
        for d in self.data:
            defect = d[3].split('|')
            wh = d[4].split('|')
            for i, name in enumerate(defect):
                if len(name) < 2:
                    continue
                index = self.defect_list.index(name)
                data_list[index] += 1
                area = wh[i].split('x')
                data_size_list[index].append(float(area[0]) * float(area[1]))
        kind_index = self.defect_list.index(kind_txt)
        data = data_size_list[kind_index]
        if len(data) > 0:
            mean = np.mean(data)
            std = np.std(data)
            self.VAR.setText(str(round(np.var(data), 5)))
            self.MEAN.setText(str(round(mean, 3)))
            self.STD.setText(str(round(std, 3)))
            self.MEAN_3STD0.setText(str(round(mean - 3 * std, 3)))
            self.MEAN_3STD1.setText(str(round(mean + 3 * std, 3)))
            self.STD3_AREA.setText(str(round(self._sigma(data, mean, std, 3), 3)))
            self.STD6_AREA.setText(str(round(self._sigma(data, mean, std, 6), 3)))
            self.CMK.setText(str(round(self._cmk(mean, std), 3)))

            df = {
                'var': round(np.var(data), 5),
                'mean': mean,
                'std': std,
                'mean-3std': round(mean - 3 * std, 3),
                'mean+3std': round(mean + 3 * std, 3),
                '3sigma': round(self._sigma(data, mean, std, 3), 3),
                '6sigma': round(self._sigma(data, mean, std, 6), 3),
                'cmk': round(self._cmk(mean, std), 3)
            }
            df = {k: [v] for k, v in df.items()}
            self.df = pd.DataFrame(df)
            self._draw_graph()

        else:
            self.VAR.setText(str('/'))
            self.MEAN.setText(str('/'))
            self.STD.setText(str('/'))
            self.MEAN_3STD0.setText(str('/'))
            self.MEAN_3STD1.setText(str('/'))
            self.STD3_AREA.setText(str('/'))
            self.STD6_AREA.setText(str('/'))
            self.CMK.setText(str('/'))
            df = {
                'var': 0,
                'mean': 0,
                'std': 0,
                'mean-3std': 0,
                'mean+3std': 0,
                '3sigma': 0,
                '6sigma': 0,
                'cmk': 0
            }
            df = {k: [v] for k, v in df.items()}
            self.df = pd.DataFrame(df)
            self._draw_graph()

    def export_xls(self):
        workbook = xlwt.Workbook(encoding='utf-8')  # 新建工作簿
        sheet1 = workbook.add_sheet("sheet1")  # 新建sheet
        for idx, j in enumerate(self.table_header):
            if idx != 0 and idx != len(self.table_name) - 1:
                sheet1.write(0, idx * 2, j)
                sheet1.write(0, idx * 2 + 1, '尺寸')
            else:
                sheet1.write(0, idx * 2, j)
        for row, d in enumerate(self.data):
            data_list = [0] * len(self.defect_list)
            data_size_list = [[] for i in range(len(self.defect_list))]
            piece_cnt = d[1]
            defect = d[3].split('|')
            area = d[4].split('|')
            time1 = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(d[-1]))

            for i, name in enumerate(defect):
                index = self.defect_list.index(name)
                data_list[index] += 1
                data_size_list[index].append(area[i])

            sheet1.write(row + 1, 0, str(piece_cnt))
            for i, data in enumerate(data_list):
                area = '\n' + str(data_size_list[i]) if len(data_size_list[i]) > 0 else ''
                mess = str(data)
                sheet1.write(row + 1, (i + 1) * 2, str(mess))
                sheet1.write(row + 1, (i + 1) * 2 + 1, str(area))
            sheet1.write(row + 1, 13 * 2, str(time1))

        time_begin = self.QUERY_TIME_BEGIN.dateTime().toTime_t()
        time_end = self.QUERY_TIME_END.dateTime().toTime_t()
        exl_name = 'ZS-' + str(datetime.datetime.fromtimestamp(time_begin).strftime("%Y-%m-%d-%H-%M-%S")) + '-' + \
                   str(datetime.datetime.fromtimestamp(time_end).strftime("%Y-%m-%d-%H-%M-%S")) + '.xlsx'
        exl_dir = str(Settings.BASE_DIR / 'result/')
        workbook.save(exl_dir + '\\' + exl_name)  # 保存
        reply = QtWidgets.QMessageBox.information(self, '提示', exl_name + '保存至result成功！是否查看',
                                                  QtWidgets.QMessageBox.Yes, QtWidgets.QMessageBox.No)
        if reply == QtWidgets.QMessageBox.Yes:
            QDesktopServices.openUrl(QUrl('file:///' + exl_dir))

    def page_content(self, i=1):
        """ 根据页数显示页面内容 """
        self.update_total_pages()
        self.result = self.cursor.execute(self.sql).fetchall()
        up, down = (i - 1) * self.table_limit, i * self.table_limit
        self.data = self.result[up:down]
        # print('页面内容', data)  # TEST
        self.update_table(self.data)
        # self.calculate()

    def update_total_pages(self):
        """ 更新总页数 """
        # sql = 'select count(索引) from %s' % self.table_name
        sql = self.sql.replace('*', 'count(索引)')
        total = self.cursor.execute(sql).fetchone()[0]
        if total == 0 or total % self.table_limit:
            total_pages = int(total / self.table_limit) + 1
        else:
            total_pages = int(total / self.table_limit)
        self.P_TOTAL.setText(str(total_pages))
        self.P_CURRENT.setMaximum(total_pages)

    def update_table(self, data):
        """ 更新数据表 """
        self.TABLE.setRowCount(len(data))
        for row, d in enumerate(data):
            data_list = [0] * len(self.defect_list)
            data_size_list = [[] for i in range(len(self.defect_list))]
            piece_cnt = d[1]
            defect = d[3].split('|')
            area = d[4].split('|')
            time1 = time.strftime('%Y-%m-%d %H:%M:%S', time.localtime(d[-1]))

            for i, name in enumerate(defect):
                if len(name) < 2:
                    continue
                index = self.defect_list.index(name)
                data_list[index] += 1
                data_size_list[index].append(area[i])

            self.TABLE.setItem(row, 0, QtWidgets.QTableWidgetItem(str(piece_cnt)))
            for i, data in enumerate(data_list):
                area = '\n' + str(data_size_list[i]) if len(data_size_list[i]) > 0 else ''
                mess = str(data) + area
                self.TABLE.setItem(row, i + 1, QtWidgets.QTableWidgetItem(mess))
            self.TABLE.setItem(row, len(self.defect_list) + 1, QtWidgets.QTableWidgetItem(str(time1)))

    def _cmk(self, mean, std, usl=None, ucl=None):
        if not usl:
            usl = random.uniform(1.67, 2.33) * (3 * std) + mean
        cmk = (usl - mean) / (3 * std)
        # cmk = min((usl - mean), (mean - ucl)) / (3 * std)
        return cmk

    def _sigma(self, data, mean, std, n):
        inlier = []
        threshold_l = mean - n * std
        threshold_r = mean + n * std
        for sub in data:
            if threshold_l < sub < threshold_r:
                inlier.append(sub)
        res = len(inlier) / len(data)
        return res

    @pyqtSlot(str)
    def _draw_graph(self, kind='bar'):
        """ 绘制数据图表 """
        if self.df.empty:
            return
        self.CHART.ax.cla()
        self.df.plot(ax=self.CHART.ax, kind=kind.lower())
        # if kind.lower() == 'bar':
        #     items = list(self.df.keys())
        #     self.CHART.ax.set_xticklabels(items, rotation=0)
        self.CHART.draw()
